package com.naiterui.ehp.bs.agent.repository;

import java.util.Date;
import java.util.List;
import java.util.Set;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.naiterui.common.repository.core.BaseJpaRepository;
import com.naiterui.ehp.bs.agent.entity.AgAgentArea;

/**
 * @author yiren
 * @since 2022/2/15
 */
@Repository
public interface IAgAgentAreaRepository extends BaseJpaRepository<AgAgentArea, Long> {
	/**
	 * 查询经纪人指定职位的区域
	 * @param agentId
	 * @param titleType
	 * @return
	 */
	List<AgAgentArea> findByAgentIdAndTitleTypeOrderById(Long agentId, Integer titleType);

	/**
	 * 查询经纪人指定职位的区域
	 * @param agentIds
	 * @param titleType
	 * @return
	 */
	List<AgAgentArea> findByAgentIdInAndTitleTypeOrderById(List<Long> agentIds, Integer titleType);

	/**
	 *
	 * @param agentId
	 * @return
	 */
	List<AgAgentArea> findByAgentIdOrderById(Long agentId);

	/**
	 * 查询指定区域经理的下属成员人数量
	 * @param areaIds
	 * @return
	 */

	@Query(value = "select count(t) from AgAgentArea t where t.areaId in (:areaIds) and t.titleType=3 and t.agentId not in (:agentIds)")
	Long countAgentNum(List<Long> areaIds, Set<Long> agentIds);

	/**
	 * 查询指定区域经纪人总数
	 * @param areaIds
	 * @param endTime
	 * @return
	 */
	@Query(value = "select count(1) from (select agent_id from ag_agent_area where area_id in (:areaIds) and created_at<:endTime group by agent_id) a", nativeQuery = true)
	Long countAgentTotal(List<Long> areaIds, String endTime);

	/**
	 *
	 * @param areaId
	 * @param titleType
	 * @return
	 */
	List<AgAgentArea> findByAreaIdAndTitleType(Long areaId, Integer titleType);

	/**
	 *
	 * @param areaIds
	 * @param titleType
	 * @return
	 */
	List<AgAgentArea> findByAreaIdInAndTitleType(List<Long> areaIds, Integer titleType);

	/**
	 * 查询省区下成员
	 * @param areaId
	 * @param manageAgentId
	 * @param endTime
	 * @param pageable
	 * @return
	 */
	@Query(value = "select t from AgAgentArea t where t.areaId=:areaId and t.createdAt<:endTime and (t.titleType=3 and t.agentId<>:manageAgentId or t.titleType=2 and t.agentId=:manageAgentId)")
	Page<AgAgentArea> findByAreaId(@Param("areaId")Long areaId, @Param("manageAgentId") Long manageAgentId, @Param("endTime") Date endTime, Pageable pageable);

    @Override
	List<AgAgentArea> findAll();

    @Query(
        value = "SELECT a.parent_id areaId, aa.area_id proviceAreaId, aa.agent_id agentId, aa.created_at createdAt FROM ag_agent_area aa LEFT JOIN ag_area a ON aa.area_id = a.id where aa.title_type=3",
        nativeQuery = true)
    List<Object[]> findAllByTitleType();
}
